<?php

/**
 * 
 * @author kras
 * @property IAnsiSQLDBDriver $Driver
 */
class TMSDataMine extends TConfigurable {
/**
 * @var IAnsiSQLDBDriver
 */	
	protected $_ioc_driver_;
	
	public function AvailableDimensions(){
		$q = "select * from type where base in ('nom','gds','srv') order by name";
		$res = $this->Driver->Fetch($q);
		$result = array();
		foreach ($res as $c)
			$result[$c['class_name']] = array(
					'classname'=>$c['class_name'],
					'caption'=>$c['name'],
					'dimensions'=>json_decode($c['attr_meta'])
			);	
		return $result;
	}
	
	public function Pivot(TDate $since, TDate $till, array $custom_dimensions = array()){
		$flds = 'date(o.added) as added, IF(o.client is NULL,0,1) as loyal, c.name as category, floor(op.order_price/1000)*1000 as price_group';
		$grp = 'date(o.added), IF(o.client is NULL,0,1), c.name, floor(op.order_price/1000)*1000';
		
		$a_flds = array();
		$a_grp = array();
		$tables = array();
		
		$i = 0;
		$j = 0;
		foreach ($custom_dimensions as $class=>$dim){
			$q = 'select * from type where class_name = :cn';
			$r = $this->Driver->Fetch($q,array('cn'=>$class));
			$alias = false;
			foreach ($r as $c){
				$alias = 'c'.$i;
				$tables[] = ' left join '.strtolower(substr($class, 1)).' '.$alias.' on '.$alias.'.id = '.(($c['base'] == 'nom')?'n.nom_id':'a.artid');
				$attr_meta = json_decode($c["attr_meta"]);
				break;
			}
			if ($alias){
				foreach ($dim as $d){
					if ($attr_meta->$d->Type == 'ref'){
						$cn = $attr_meta->$d->RefClass;
						$q = 'select * from type where class_name = :cn';
						$r2 = $this->Driver->Fetch($q,array('cn'=>$cn));
						$i++;
						foreach ($r2 as $c){
							$alias2 = 'c'.$i;
							$tbl = ' left join '.strtolower(substr($cn, 1)).' '.$alias2.' on '.$alias2.'.id = '.$alias.'.'.strtolower($d);
							$attr_meta2 = json_decode($c["attr_meta"]);
							break;
						}
						
						if ($alias2){
							$found = false;
							foreach ($attr_meta2 as $nm=>$meta)
								if ($meta->Type == TItemPropertyType::PT_STRING){
									$a_flds[] = $alias2.'.'.strtolower($nm).' as dim'.$j;
									$a_grp[] = $alias2.'.'.strtolower($nm);
									$j++;
									$found = true;
									break;
								}
							if ($found){
								$tables[] = $tbl;
								continue;
							}
						}		
					}
					
					$a_flds[] = $alias.'.'.strtolower($d).' as dim'.$j;
					$a_grp[] = $alias.'.'.strtolower($d);
					$j++;	
				}
			}
			$i++;
		}
		
		$q = 'select '.$flds.((!empty($a_flds))?',':'').join(', ',$a_flds).', sum(op.quantaty) as quantaty, sum(op.order_price) as amount 
			from `order_position` op
			inner join `order` o on o.orderid = op.order
			inner join `article` a on a.artid = op.article
			inner join `nomenclature` n on n.nom_id = a.nomenclature
			left join `catalog` c on c.cid = n.catalog
			'.join("\n",$tables).' 
			where o.added between :since and :till
			group by '.$grp.((!empty($a_grp))?',':'').join(', ',$a_grp).'
			order by o.added';
		return $this->Driver->Fetch($q,array('since'=>$since,'till'=>$till));
	}
}